package day09;

import lombok.Data;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
@Author: 王少杰
@Date: 2025/1/9
@Time: 14:21
@FileName: Mysql.java
@描述:
学生表:
    id primary key autoincrement
    name string
    age int
    score float


数据库中的一张表与一个Java类对应。

连接数据库  connect
执行sql  executeUpdate  executeQuery
    插入insertStudent()、修改、删除、创建表  返回影响了多少行
    查询  表中的数据
断开连接
*/
public class MysqlDB {
    Connection conn;//创建一个连接对象
    public static final String URL = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8&useSSL=false";
    public static final String USER = "root";
    public static final String PASSWORD = "123456";

    public void connect(){
        try {
            conn = DriverManager.getConnection(URL,USER,PASSWORD);
            System.out.println("连接数据库成功");
        } catch (SQLException e) {
            System.out.println("连接数据库异常，异常信息为:" + e.getMessage());
        }
    }

    public void disconnect(){
        try {
            conn.close();
            System.out.println("断开数据库连接成功");
        } catch (SQLException e) {
            System.out.println("断开数据库连接异常，异常信息为:" + e.getMessage());
        }
    }

    public void createTable(){
        String sql = "create table if not exists student (\n" +
                "    id integer primary key auto_increment,\n" +
                "    name varchar(32),\n" +
                "    age int,\n" +
                "    score float\n" +
                ") default charset=utf8mb4;\n";

        //Statement \ PreparedStatement 类实现的执行sql语句的方法
        //PreparedStatement比Statement更安全,Statement是拼接的sql语句，有sql注入的问题。
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.execute();//执行sql语句
            ps.close();
            System.out.println("执行" + sql + "成功");
        } catch (SQLException e) {
            System.out.println("执行" + sql + "异常，异常信息为:" + e.getMessage());
        }
    }

    public boolean insertStudent(Student student){
        String sql = "insert into student(name,age,score) values(?,?,?)";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setInt(2,student.getAge());
            preparedStatement.setFloat(3,student.getScore());
            int result = preparedStatement.executeUpdate();
            preparedStatement.close();
            System.out.println("执行" + sql + "成功");
            return result == 1;//影响条数为1,表示插入成功了
        } catch (SQLException e) {
            System.out.println("执行插入操作的:" + sql + "异常，异常信息为:" + e.getMessage());
            return false;
        }
    }
    //修改
    public boolean updateStudent(Student student){
        String sql = "update student set name=?,age=?,score=? where id=?";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setInt(2,student.getAge());
            preparedStatement.setFloat(3,student.getScore());
            preparedStatement.setInt(4,student.getId());
            int result = preparedStatement.executeUpdate();
            preparedStatement.close();
            System.out.println("执行" + sql + "成功");
            return result == 1;
        } catch (SQLException e) {
            System.out.println("执行更新操作的:" + sql + "异常，异常信息为:" + e.getMessage());
            return false;
        }
    }
    //删除
    public boolean deleteStudent(int id){
        String sql = "delete from student where id=?";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            int result = preparedStatement.executeUpdate();
            preparedStatement.close();
            System.out.println("执行" + sql + "成功");
            return result == 1;
        } catch (SQLException e) {
            System.out.println("执行删除操作的:" + sql + "异常，异常信息为:" + e.getMessage());
            return false;
        }
    }
    public boolean deleteAllStudent(String name){
        String sql = "delete from student where name=?";
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,name);
            int result = preparedStatement.executeUpdate();
            preparedStatement.close();
            return result == 1;
        } catch (SQLException e) {
            System.out.println("执行删除操作的:" + sql + "异常，异常信息为:" + e.getMessage());
            return false;
        }
    }
    //查找
    public List<Student> findAllStudent(){
        String sql = "select id,name,age,score from student";
        List<Student> list = new ArrayList<>();
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet != null) {
                while(resultSet.next()){
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");
                    float score = resultSet.getFloat("score");
                    Student student = new Student(id,name,age,score);
                    list.add(student);//将学生加入列表
                }
            }
            preparedStatement.close();
            System.out.println("执行" + sql + "成功，查询数量为: " + list.size());
        } catch (SQLException e) {
            System.out.println("执行删除操作的:" + sql + "异常，异常信息为:" + e.getMessage());
        }
        return list;
    }

    public List<Student> findStudent(String keyword){
        String sql = "select id,name,age,score from student where name like ? or score like ?";
        List<Student> list = new ArrayList<>();
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,"%"+keyword+"%");
            preparedStatement.setString(2,"%"+keyword+"%");
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet != null) {
                while(resultSet.next()){
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");
                    float score = resultSet.getFloat("score");
                    Student student = new Student(id,name,age,score);
                    list.add(student);//将学生加入列表
                }
            }
            preparedStatement.close();
            System.out.println("执行" + sql + "成功，查询数量为: " + list.size());
        } catch (SQLException e) {
            System.out.println("执行删除操作的:" + sql + "异常，异常信息为:" + e.getMessage());
        }
        return list;
    }
}

@Data
class Student{
    private int id;
    private String name;
    private int age;
    float score;

    public Student(String name, int age, float score) {
        this.name = name;
        this.age = age;
        this.score = score;
    }

    public Student(int id, String name, int age, float score) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.score = score;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", score=" + score +
                '}';
    }
}

